package com.donge.excelreport.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.donge.excelreport.dto.ListShowDTO;
import com.donge.excelreport.dto.ListShowValue;
import com.donge.excelreport.mapper.ListShowMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

@Service
@Slf4j
public class ReportService {
    @Autowired
    ListShowMapper listShowMapper;


//    public static void main(String[] args) {
//        String excelFilePath = "D:\\excel\\testlist.xlsx";
//        readExcel(excelFilePath);
//    }
    public  void readExcel(String excelFilePath) {
        try (FileInputStream file = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(file)) {
            Map<String,List<ListShowDTO>> map = new HashMap<>();
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                String sheetName = sheet.getSheetName();
                if (sheet == null) {
                    continue;
                }

                List<ListShowDTO> listShowDTOS = new ArrayList<>();
                for (Row row : sheet) {
                    int rowNum = row.getRowNum();
                    if (rowNum == 0){
                        continue;
                    }
                    int i = 0;
                    ListShowDTO listShowDTO = new ListShowDTO();
                    for (Cell cell : row) {
                        // 根据你的需求处理单元格数据
                        if (i == 3){
                            String cellValueAsString = getCellValueAsString(cell);
                            String[] split = cellValueAsString.split(" ");
                            if (split.length >1){
                                StringJoiner joiner = new StringJoiner("");
                                for (int j = 0; j < split.length; j++) {
                                    if(j == 0){
                                        joiner.add(split[j].toLowerCase());
                                    }else {
                                        String str = split[j];
                                        String fieldName = Character.toUpperCase(str.charAt(0))+ str.substring(1).toLowerCase();
                                        joiner.add(fieldName);
                                    }
                                }
                                listShowDTO.setFieldName(joiner.toString());
                            }else {
                                listShowDTO.setFieldName(cellValueAsString.toLowerCase());
                            }

                        }

                        if (i==8){
                            listShowDTO.setDisplay(getCellValueAsString(cell));
                        }
                        i++;
                    }
                    if (!StringUtils.isEmpty(listShowDTO.getDisplay())){
                        listShowDTOS.add(listShowDTO);
                    }
                }
                //map.put(sheetName,listShowDTOS);
                //插入数据库
                printJson(sheetName,listShowDTOS);
            }


        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private  void printJson(String name,List<ListShowDTO> listShowDTOS) {
        List<ListShowValue> listShowValues = new ArrayList<>();
        for (ListShowDTO listShowDTO : listShowDTOS) {
            ListShowValue listShowValue = new ListShowValue();
            listShowValue.setFieldName(listShowDTO.getFieldName());
            listShowValues.add(listShowValue);
        }
        ListShowValue listShowValue = new ListShowValue();
        listShowValue.setFieldName("operators");
        listShowValue.setDisable(true);
        listShowValue.setMove(false);
        listShowValue.setFixed("right");
        listShowValues.add(listShowValue);


        JSONArray array= JSONArray.parseArray(JSON.toJSONString(listShowValues));
        System.out.println(array);
        listShowMapper.insert(name,array.toJSONString());
    }

    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return String.valueOf(cell.getCellFormula());
            default:
                return "";
        }
    }

//    public static void createExcel() {
//        try (Workbook workbook = new XSSFWorkbook()) {
//            //File tmpFile = Files.createTempFile("", ".xlsx").toFile();
//            // 在指定的目录下，创建临时文件
//            File tmpFile = File.createTempFile("excel", ".xlsx", new File("D://excel"));
//            tmpFile.toPath().toAbsolutePath().toString();
//            log.info("[DR]Tmp excel file -> {}", tmpFile);
//            XSSFFont headFont = (XSSFFont) workbook.createFont();
//            headFont.setBold(true);
//            XSSFCellStyle headStyle = (XSSFCellStyle) workbook.createCellStyle();
//            headStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
//            headStyle.setFont(headFont);
//
//            doCreate(workbook, headStyle);
//            workbook.write(new FileOutputStream(tmpFile));
//        } catch (IOException e) {
//            throw new RuntimeException(e);
//        }
//
//
//
//
//
//    }
//
//
//    private static void doCreate(Workbook workbook, XSSFCellStyle headStyle) {
//        Sheet sheet = workbook.createSheet("Sheet 1");
//        int startRow = 0;
//        List<String> headList = Arrays.asList(new String[]{"Client a/c", "Type", "Currency", "Debit Balance", "0d-5d", "6d-1M", ">1M", "0d-5d", "6d-1M", ">1M", "0d-5d", "6d-1M", ">1M", "Total Market Value", "Market Value After Haircut"});
//        Row head = sheet.createRow(startRow);
//        Row row = sheet.createRow(1);
//        for (int i = 0; i < headList.size(); i++) {
//            if (i < 4 || i > 12) {
//                CellRangeAddress region = new CellRangeAddress(0, 1, i, i);
//                sheet.addMergedRegion(region);
//                Cell cell = head.createCell(i);
//                cell.setCellValue(headList.get(i));
//                headStyle.setVerticalAlignment(VerticalAlignment.TOP); // 设置垂直
//                cell.setCellStyle(headStyle);
//            }
//
//            if (i==4){
//                mergeHead(headStyle, sheet,row, headList, head, i,"Unsettled amount");
//            }else if (i==7){
//                mergeHead(headStyle, sheet, row,headList, head, i,"Quantity(Trade Day Basis)");
//            }else if (i==10){
//                mergeHead(headStyle, sheet, row,headList, head, i,"Market Value(Trade Day Basis)");
//            }
//
//        }
//    }
//
//    private static void mergeHead(XSSFCellStyle headStyle, Sheet sheet, Row row ,List<String> headList, Row head, int i,String value) {
//        CellRangeAddress region = new CellRangeAddress(0, 0, i, i + 2);
//        sheet.addMergedRegion(region);
//        Cell cell = head.createCell(i);
//        cell.setCellValue(value);
//        cell.setCellStyle(headStyle);
//
//        for (int j = i; j <=i+2 ; j++) {
//            Cell cell1 = row.createCell(j);
//            cell1.setCellValue(headList.get(j));
//            cell1.setCellStyle(headStyle);
//        }
//    }
}
